
const connection=require("../app/databases")
const sqlFragment=`select  m.id id,m.content content,m.createAt  createTime,m.updateAt updateTime,JSON_OBJECT('id',u.id,'姓名',u.name) autor from moment m left join user u on m.user_id=u.id`;
class MonmentService{
  
    async  insertContent(ctx){
        const statents="insert  into `moment` (content,user_id)  values (?,?)"
        const [result]=await connection.execute(statents,[ctx.request.body.content,ctx.user.id])
        console.log(result)
         return result;
        
    }
    async getMomentById(momentid){
        const statement=`select m.id id,m.content content,m.createAt createTime,m.updateAt updateTime,
        json_object("id",u.id,"name",u.name,"avatar_url",u.avatar_url) as autor,
        (SELECT if(count(m.id),json_arrayagg(json_object("id",c.id,"content",c.content,"autor",json_object("id",u.id,"name",u.name,"avatar_url",u.avatar_url))),null) from comment c LEFT JOIN user u on c.user_id=u.id  where m.id=c.moment_id) 
         as comment,
         if(count(l.id),JSON_ARRAYAGG(json_object("id",l.id,"name",l.name)),null)  as label,
         (select  json_arrayagg(concat('http://localhost:8000/moment/images/',f.filename)) from file f  where f.moment_id=m.id) as images  
        from moment m
        LEFT JOIN  user u on m.user_id=u.id
        LEFT JOIN moment_label ml on m.id=ml.moment_id
        RIGHT JOIN label l on ml.label_id=l.id
        where m.id=?
        GROUP BY m.id`
       
        const result=await connection.execute(statement,[momentid])
        return result[0];
    }
    async  getQueryList(offset,size){
        
        const statement=`select m.id,m.content,m.createAt,m.updateAt,json_object(u.id,u.name) as autor,(select count(*) from comment c where c.moment_id=m.id) commentcount,
        (select count(*) from label l where  l.id=ml.label_id) as labelcount
        from moment m 
        left join user u on   m.user_id=u.id
        LEFT JOIN moment_label ml on m.id=ml.moment_id
        LEFT JOIN label l on l.id=ml.label_id 
        limit ?,?`
        console.log(size)
        const result=await connection.execute(statement,[offset,size]);
        console.log(result)
        //返回的是列表
        return result[0]; 
    }

}
module.exports=new MonmentService()